using System;
using System.Data;
using System.Data.SqlClient;

namespace Company.BLL.SXXK.ThanhKhoan
{
    public partial class NPLTuCungUng : EntityBase
    {
        #region Private members.

        protected int _LanThanhLy;
        protected string _MaDoanhNghiep = String.Empty;
        protected string _MaHaiQuan = String.Empty;
        protected string _MaNPL = String.Empty;
        protected string _SoHoaDon = String.Empty;
        protected DateTime _NgayHoaDon = new DateTime(1900, 01, 01);
        protected decimal _SoLuongCungUng;

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Properties.

        public int LanThanhLy
        {
            set { this._LanThanhLy = value; }
            get { return this._LanThanhLy; }
        }
        public string MaDoanhNghiep
        {
            set { this._MaDoanhNghiep = value; }
            get { return this._MaDoanhNghiep; }
        }
        public string MaHaiQuan
        {
            set { this._MaHaiQuan = value; }
            get { return this._MaHaiQuan; }
        }
        public string MaNPL
        {
            set { this._MaNPL = value; }
            get { return this._MaNPL; }
        }
        public string SoHoaDon
        {
            set { this._SoHoaDon = value; }
            get { return this._SoHoaDon; }
        }
        public DateTime NgayHoaDon
        {
            set { this._NgayHoaDon = value; }
            get { return this._NgayHoaDon; }
        }
        public decimal SoLuongCungUng
        {
            set { this._SoLuongCungUng = value; }
            get { return this._SoLuongCungUng; }
        }

        //---------------------------------------------------------------------------------------------

        public bool IsExist
        {
            get
            {
                return this.Load();
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Select methods.

        public bool Load()
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_Load";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@MaNPL", SqlDbType.VarChar, this._MaNPL);

            IDataReader reader = this.db.ExecuteReader(dbCommand);
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) this._LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this._MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this._MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) this._MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoaDon"))) this._SoHoaDon = reader.GetString(reader.GetOrdinal("SoHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayHoaDon"))) this._NgayHoaDon = reader.GetDateTime(reader.GetOrdinal("NgayHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuongCungUng"))) this._SoLuongCungUng = reader.GetDecimal(reader.GetOrdinal("SoLuongCungUng"));
                return true;
            }
            return false;
        }

        //---------------------------------------------------------------------------------------------

        public NPLTuCungUngCollection SelectCollectionBy_MaDoanhNghiep_AND_LanThanhLy_AND_MaHaiQuan()
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectBy_MaDoanhNghiep_AND_LanThanhLy_AND_MaHaiQuan";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);

            NPLTuCungUngCollection collection = new NPLTuCungUngCollection();
            IDataReader reader = this.db.ExecuteReader(dbCommand);
            while (reader.Read())
            {
                NPLTuCungUng entity = new NPLTuCungUng();
                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) entity.LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) entity.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) entity.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) entity.MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoaDon"))) entity.SoHoaDon = reader.GetString(reader.GetOrdinal("SoHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayHoaDon"))) entity.NgayHoaDon = reader.GetDateTime(reader.GetOrdinal("NgayHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuongCungUng"))) entity.SoLuongCungUng = reader.GetDecimal(reader.GetOrdinal("SoLuongCungUng"));
                collection.Add(entity);
            }
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        public DataSet SelectBy_MaDoanhNghiep_AND_LanThanhLy_AND_MaHaiQuan()
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectBy_MaDoanhNghiep_AND_LanThanhLy_AND_MaHaiQuan";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);

            return this.db.ExecuteDataSet(dbCommand);
        }
        //---------------------------------------------------------------------------------------------

        public DataSet SelectAll()
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectAll";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            return this.db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public IDataReader SelectReaderAll()
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectAll";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            return this.db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public DataSet SelectDynamic(string whereCondition, string orderByExpression)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectDynamic";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            this.db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return this.db.ExecuteDataSet(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public IDataReader SelectReaderDynamic(string whereCondition, string orderByExpression)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_SelectDynamic";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@WhereCondition", SqlDbType.NVarChar, whereCondition);
            this.db.AddInParameter(dbCommand, "@OrderByExpression", SqlDbType.NVarChar, orderByExpression);

            return this.db.ExecuteReader(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public NPLTuCungUngCollection SelectCollectionAll()
        {
            NPLTuCungUngCollection collection = new NPLTuCungUngCollection();

            IDataReader reader = this.SelectReaderAll();
            while (reader.Read())
            {
                NPLTuCungUng entity = new NPLTuCungUng();

                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) entity.LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) entity.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) entity.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) entity.MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoaDon"))) entity.SoHoaDon = reader.GetString(reader.GetOrdinal("SoHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayHoaDon"))) entity.NgayHoaDon = reader.GetDateTime(reader.GetOrdinal("NgayHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuongCungUng"))) entity.SoLuongCungUng = reader.GetDecimal(reader.GetOrdinal("SoLuongCungUng"));
                collection.Add(entity);
            }
            return collection;
        }

        //---------------------------------------------------------------------------------------------

        public NPLTuCungUngCollection SelectCollectionDynamic(string whereCondition, string orderByExpression)
        {
            NPLTuCungUngCollection collection = new NPLTuCungUngCollection();

            IDataReader reader = this.SelectReaderDynamic(whereCondition, orderByExpression);
            while (reader.Read())
            {
                NPLTuCungUng entity = new NPLTuCungUng();

                if (!reader.IsDBNull(reader.GetOrdinal("LanThanhLy"))) entity.LanThanhLy = reader.GetInt32(reader.GetOrdinal("LanThanhLy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) entity.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) entity.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaNPL"))) entity.MaNPL = reader.GetString(reader.GetOrdinal("MaNPL"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoHoaDon"))) entity.SoHoaDon = reader.GetString(reader.GetOrdinal("SoHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayHoaDon"))) entity.NgayHoaDon = reader.GetDateTime(reader.GetOrdinal("NgayHoaDon"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuongCungUng"))) entity.SoLuongCungUng = reader.GetDecimal(reader.GetOrdinal("SoLuongCungUng"));
                collection.Add(entity);
            }
            return collection;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert methods.

        public long Insert()
        {
            return this.InsertTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public long InsertTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_Insert";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@MaNPL", SqlDbType.VarChar, this._MaNPL);
            this.db.AddInParameter(dbCommand, "@SoHoaDon", SqlDbType.VarChar, this._SoHoaDon);
            this.db.AddInParameter(dbCommand, "@NgayHoaDon", SqlDbType.DateTime, this._NgayHoaDon);
            this.db.AddInParameter(dbCommand, "@SoLuongCungUng", SqlDbType.Decimal, this._SoLuongCungUng);

            if (transaction != null)
            {
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            }
            else
            {
                return this.db.ExecuteNonQuery(dbCommand);
            }
        }

        //---------------------------------------------------------------------------------------------

        public bool Insert(NPLTuCungUngCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (NPLTuCungUng item in collection)
                    {
                        if (item.InsertTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }

        //---------------------------------------------------------------------------------------------		

        public void InsertTransaction(SqlTransaction transaction, NPLTuCungUngCollection collection)
        {
            foreach (NPLTuCungUng item in collection)
            {
                item.InsertTransaction(transaction);
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Insert / Update methods.
        public int InsertUpdate()
        {
            return this.InsertUpdateTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int InsertUpdateTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_InsertUpdate";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);
            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@MaNPL", SqlDbType.VarChar, this._MaNPL);
            this.db.AddInParameter(dbCommand, "@SoHoaDon", SqlDbType.VarChar, this._SoHoaDon);
            this.db.AddInParameter(dbCommand, "@NgayHoaDon", SqlDbType.DateTime, this._NgayHoaDon);
            this.db.AddInParameter(dbCommand, "@SoLuongCungUng", SqlDbType.Decimal, this._SoLuongCungUng);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public bool InsertUpdate(NPLTuCungUngCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (NPLTuCungUng item in collection)
                    {
                        if (item.InsertUpdateTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Update methods.

        public int Update()
        {
            return this.UpdateTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int UpdateTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_Update";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@MaNPL", SqlDbType.VarChar, this._MaNPL);
            this.db.AddInParameter(dbCommand, "@SoHoaDon", SqlDbType.VarChar, this._SoHoaDon);
            this.db.AddInParameter(dbCommand, "@NgayHoaDon", SqlDbType.DateTime, this._NgayHoaDon);
            this.db.AddInParameter(dbCommand, "@SoLuongCungUng", SqlDbType.Decimal, this._SoLuongCungUng);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public void UpdateCollection(NPLTuCungUngCollection collection, SqlTransaction transaction)
        {
            foreach (NPLTuCungUng item in collection)
            {
                item.UpdateTransaction(transaction);
            }
        }

        #endregion

        //---------------------------------------------------------------------------------------------

        #region Delete methods.

        public int Delete()
        {
            return this.DeleteTransaction(null);
        }

        //---------------------------------------------------------------------------------------------

        public int DeleteTransaction(SqlTransaction transaction)
        {
            string spName = "p_SXXK_ThanhLy_NPLTuCungUng_Delete";
            SqlCommand dbCommand = (SqlCommand)this.db.GetStoredProcCommand(spName);

            this.db.AddInParameter(dbCommand, "@LanThanhLy", SqlDbType.Int, this._LanThanhLy);
            this.db.AddInParameter(dbCommand, "@MaDoanhNghiep", SqlDbType.VarChar, this._MaDoanhNghiep);
            this.db.AddInParameter(dbCommand, "@MaHaiQuan", SqlDbType.Char, this._MaHaiQuan);
            this.db.AddInParameter(dbCommand, "@MaNPL", SqlDbType.VarChar, this._MaNPL);

            if (transaction != null)
                return this.db.ExecuteNonQuery(dbCommand, transaction);
            else
                return this.db.ExecuteNonQuery(dbCommand);
        }

        //---------------------------------------------------------------------------------------------

        public void DeleteCollection(NPLTuCungUngCollection collection, SqlTransaction transaction)
        {
            foreach (NPLTuCungUng item in collection)
            {
                item.DeleteTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------

        public bool DeleteCollection(NPLTuCungUngCollection collection)
        {
            bool ret;
            using (SqlConnection connection = (SqlConnection)db.CreateConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    bool ret01 = true;
                    foreach (NPLTuCungUng item in collection)
                    {
                        if (item.DeleteTransaction(transaction) <= 0)
                        {
                            ret01 = false;
                            break;
                        }
                    }
                    if (ret01)
                    {
                        transaction.Commit();
                        ret = true;
                    }
                    else
                    {
                        transaction.Rollback();
                        ret = false;
                    }
                }
                catch
                {
                    ret = false;
                    transaction.Rollback();
                }
                finally
                {
                    connection.Close();
                }
            }
            return ret;
        }
        #endregion

        public static void updateDatabase(DataSet ds, SqlTransaction transaction)
        {
            foreach (DataRow row in ds.Tables[0].Rows)
            {
                NPLTuCungUng npl = new NPLTuCungUng();
                npl.SoLuongCungUng = Convert.ToDecimal(row["LUONG"]);
                npl._LanThanhLy = Convert.ToInt32(row["LAN_TL"].ToString());
                npl._MaDoanhNghiep = (row["MA_DV"].ToString());
                npl._MaHaiQuan = (row["MA_HQ"].ToString());                
                npl._MaNPL = (row["MA_NPL"].ToString());
                if (row["SO_HD"].ToString()!="")
                    npl.SoHoaDon = (row["SO_HD"].ToString());
                if (row["NGAY_HD"].ToString()!="")
                    npl.NgayHoaDon = Convert.ToDateTime(row["NGAY_HD"].ToString());
                npl.InsertUpdateTransaction(transaction);
            }
        }

    }
}